package com.sophossystems.client.asmon.dao.spring;


import com.sophossystems.client.asmon.dao.UserDao;

import com.sophossystems.client.asmon.dto.UserDTO;
import com.sophossystems.client.asmon.dto.UserPkDTO;
import com.sophossystems.client.asmon.exceptions.DaoException;
import com.sophossystems.client.asmon.hibernate.User;

import java.util.ArrayList;

import java.util.List;

import java.sql.SQLException;

import org.apache.log4j.Logger;
import org.hibernate.HibernateException;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.transform.Transformers;

import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.transaction.annotation.Transactional;

public class UserDaoImpl extends AbstractDAO implements UserDao {

	
	
	private static Logger log = Logger.getLogger(UserDaoImpl.class);
	/**
	 * Method 'insert'
	 * 
	 * @param dto
	 * @return ActivityPk
	 */
	
	@Override
	@Transactional
	public UserPkDTO insert(final UserDTO dto) {
		UserPkDTO exito = null;
		log.debug("UserDaoImpl. Insert.En el insert");
		try {
			exito = (UserPkDTO) getHibernateTemplate().execute(
					new HibernateCallback() {
						/**
						 * {@inheritDoc}
						 * 
						 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
						 */
						@Override
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							Transaction tx = session.beginTransaction();
							UserPkDTO pk = null;
							User user = new User();
							user.mapper(dto);
							session.save(user);
							tx.commit();
							
							try {
								UserPkDTO res = findLastInsert();
								pk = new UserPkDTO();
								pk.setIdUser(res.getIdUser());
								
							} catch (DaoException es) {
								
								throw new HibernateException("Insercion: " +es.getMessage());
							}
							return pk;

						}

					});
			// / return exito;
		} catch (final Exception e) {
			logger.error("Error: UserDaoImpl, cause: " + e.getMessage(), e);

		}
		return exito;
	}

	/**
	 * Updates a single row in the Activity table.
	 */

	@Override
	@Transactional
	public void update(final UserDTO dto)
			throws DaoException {
		try {
			getHibernateTemplate().execute(new HibernateCallback() {
				/**
				 * {@inheritDoc}
				 * 
				 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
				 */
				@Override
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					UserPkDTO pk = null;
					UserDTO res = null;
					Transaction tx = session.beginTransaction();
					// Aqui primero se busca si existe el registro
					try {
						res = findByPrimaryKey(dto
								.getIdUser());
						pk = new UserPkDTO();
						pk.setIdUser(res.getIdUser());
					} catch (DaoException es) {
						throw new HibernateException(
								"Update UserDaoImpl: "
										+ es.getMessage());
					}
					///aqui se actualizar los campos y con los de la entrada  
					//cuidado de actualiza todo.
					User persis = new User();
					persis.mapper(dto);
					persis.setIdUser(res.getIdUser());
					session.update(persis);
					tx.commit();
					return null;
				}
			});
		} catch (final Exception e) {
			logger.error("Error: getActivity, cause: " + e.getMessage(), e);
			throw new DaoException("Error: getActivity, cause: "
					+ e.getMessage());
		}

	}

	/**
	 * Deletes a single row in the Activity table.
	 */

	@Override
	@Transactional
	public void delete(final UserPkDTO pk) throws DaoException {

		try {
			getHibernateTemplate().execute(new HibernateCallback() {
				/**
				 * {@inheritDoc}
				 * 
				 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
				 */
				@Override
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					Transaction tx = session.beginTransaction();
					User user = (User) session.load(User.class, pk.getIdUser());
					session.delete(user);
					tx.commit();
					return null;
				}
			});  
		} catch (final Exception e) {
			logger.error("Error: deleteUser, cause: " + e.getMessage(), e);
			throw new DaoException("Error: DeleteUser, cause: "
					+ e.getMessage());
		}
	}

	/**
	 * Method 'getTableName'
	 * 
	 * @return String
	 */
	public String getTableName() {
		return "User";
	}

	/**
	 * Returns all rows from the User table that match the criteria 'idUser =
	 * :idUser'.
	 */
	@Override
	@Transactional
	@SuppressWarnings("unchecked")
	public UserDTO findByPrimaryKey(final int idUser) throws DaoException {
		UserDTO usuario = null;
		try {
			usuario = (UserDTO) getHibernateTemplate().execute(
					new HibernateCallback() {
						/**
						 * {@inheritDoc}
						 * 
						 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
						 */
						@Override
						
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							UserDTO usuario = null;
							log.debug("En UserDaoImpl findByPrimaryKey");
							String query = "SELECT u.idUser as idUser, u.username as username," +
									" u.password as password, u.idRole as idRole, u.name as name," +
									" u.apellido as apellido, u.segundoApellido as segundoApellido," +
									" u.ipaddress as ipaddress, u.lastlogin as lastlogin, u.status as status" +
									" FROM " + getTableName() + " u WHERE u.idUser = :idUser";
							log.debug("Se busco en findByPrimaryKey con idUser"+idUser);
							List<UserDTO> lista = session
									.createQuery(query)
									.setInteger("idUser", idUser)
									.setResultTransformer(
											Transformers
													.aliasToBean(UserDTO.class))
									.list();
							if (lista != null && lista.size() > 0) {
								log.debug("Se encotraron registro con el Primary Key de user Dao Impl - Tamaño:"+
							lista.size());
								usuario = lista.get(0);
							} else {
								log.debug("No se encotraron registros el Primary Key de user Dao Impl");
							}

							return usuario;
						}
					});
		} catch (Exception e) {
			logger.error("Error: TipoPersona, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return usuario;

	}

	/**
	 * Returns all rows from the User table that match the criteria ''.
	 */
	@Override
	@Transactional
	@SuppressWarnings("unchecked")
	public List<UserDTO> findAll() throws DaoException {
		List<UserDTO> usuarios = null;

		try {
			usuarios = (List<UserDTO>) getHibernateTemplate().execute(
					new HibernateCallback() {
						/**
						 * {@inheritDoc}
						 * 
						 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
						 */
						@Override
					

						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
						
							String hquery = "SELECT u.idUser as idUser, u.username as username," +
									" u.password as password, u.idRole as idRole, u.name as name," +
									" u.apellido as apellido, u.segundoApellido as segundoApellido," +
									" u.ipaddress as ipaddress, u.lastlogin as lastlogin, u.status as status" +
									" FROM " + getTableName() + " u ORDER BY u.idUser";
							List<UserDTO> lista = session
									.createQuery(hquery)
									.setResultTransformer(
											Transformers
													.aliasToBean(UserDTO.class))
									.list();
							return lista;
						}
					});
		} catch (Exception e) {
			logger.error("Error: User, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return usuarios;
	}

	/**
	 * Returns all rows from the User table that match the criteria 'idUser =
	 * :idUser'.
	 */
	@Override
	@Transactional
	public List<UserDTO> findWhereIdUserEquals(int idUser) throws DaoException {

		List<UserDTO> lista = new ArrayList<UserDTO>();

		UserDTO userio = this.findByPrimaryKey(idUser);
		lista.add(userio);
		return lista;

	}

	/**
	 * Returns all rows from the User table that match the criteria 'username =
	 * :username'.
	 */
	@Override
	@Transactional
	@SuppressWarnings("unchecked")
	public List<UserDTO> findWhereUsernameEquals(final String username)
			throws DaoException {
		log.debug("UserDaoImpl.findWhereUsernameEquals");
		List<UserDTO> usuarios = null;

		try {
			usuarios = (List<UserDTO>) getHibernateTemplate().execute(
					new HibernateCallback() {
						/**
						 * {@inheritDoc}
						 * 
						 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
						 */
						@Override
					
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							
							String hquery = "SELECT u.idUser as idUser, u.username as username," +
									" u.password as password, u.idRole as idRole, u.name as name," +
									" u.apellido as apellido, u.segundoApellido as segundoApellido," +
									" u.ipaddress as ipaddress, u.lastlogin as lastlogin, u.status as status" +
									" FROM " + getTableName() + " u WHERE u.username = :username ORDER BY u.username";
							List<UserDTO> lista = session
									.createQuery(hquery)
									.setString("username", username)
									.setResultTransformer(
											Transformers
													.aliasToBean(UserDTO.class))
									.list();
							return lista;
						}
					});
		} catch (Exception e) {
			logger.error("Error: User, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return usuarios;
	}

	@Override
	public UserDTO login(final String username, final String password) throws DaoException {
		UserDTO usuario = null;
		
		try {
			usuario = (UserDTO) getHibernateTemplate().execute(
					new HibernateCallback() {
						/**
						 * {@inheritDoc}
						 * 
						 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
						 */
						@Override
						@SuppressWarnings("unchecked")
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							UserDTO usuario = null;
							log.debug("Entrando al metodo de login\n ");
							String query = "SELECT u.idUser as idUser, u.username as username," +
									" u.password as password, u.idRole as idRole, u.name as name," +
									" u.apellido as apellido, u.segundoApellido as segundoApellido," +
									" u.ipaddress as ipaddress, u.lastlogin as lastlogin, u.status as status" +
									" FROM " + getTableName() + " u WHERE u.username = :username and u.password = :password";

							List<UserDTO> lista = session
									.createQuery(query)
									.setString("username", username)
									.setString("password", password)
									.setResultTransformer(
											Transformers
													.aliasToBean(UserDTO.class))
									.list();
							log.debug("El metodo: Tamaño: "+ lista.size() +"EL password: "+password+" Usuario: "+username);
							if (lista != null && lista.size() > 0) {
								usuario = lista.get(0);
							} else {
							}

							return usuario;
						}
					});
		} catch (Exception e) {
			logger.error("Error: TipoPersona, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return usuario;
	}

	@Override
	public void changePassword( final UserDTO dto) throws DaoException {
		try {
			getHibernateTemplate().execute(new HibernateCallback() {
				/**
				 * {@inheritDoc}
				 * 
				 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
				 */
				@Override
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					Transaction tx = session.beginTransaction();
					String hqlUpdate = "UPDATE "
							+ getTableName()
							+ " SET  password = :password"
							+ " WHERE idUser = :username";
					session.createQuery(hqlUpdate)
							
							.setString("password", dto.getPassword())
							
							.setInteger("username", dto.getIdUser())
							.executeUpdate();
					tx.commit();
					return null;
				}
			});
		} catch (final Exception e) {
			logger.error("Error: getActivity, cause: " + e.getMessage(), e);
			throw new DaoException("Error: getActivity, cause: "
					+ e.getMessage());
		}

		
	}

	@Override
	public UserPkDTO findLastInsert() throws DaoException {
		UserPkDTO user = null;
		try {
			user= (UserPkDTO) getHibernateTemplate().execute(
					new HibernateCallback() {

						@Override
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							
							Query oQuery = session
									.createQuery("SELECT max(u.idUser) as idUser FROM "
											+ getTableName()
											+ " u ");
							
							UserPkDTO userDto = (UserPkDTO) oQuery
									.setResultTransformer(Transformers
											.aliasToBean(UserPkDTO.class)).list().get(0);
							return userDto;
						}
					});
		
		} catch (Exception e) {
			logger.error("Error: UserDaoImpl, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return user;

	}	

}
